package cn.chen.sparkproject.test;

import java.sql.*;

public class JdbcCRUD {

    public static void main(String[] args) {
//        insert();
//        update();
//        delete();
//        select();
        preparedStatement();
    }

    private static void preparedStatement() {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/session?characterEncoding=utf8","root","root");

            String sql = "insert into test_user(name, age) values(?, ?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"chen1");
            pstmt.setInt(2,21);

            int result = pstmt.executeUpdate( );
            System.out.println("SQL 语句影响了："+result+"行");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(pstmt != null){
                    pstmt.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void select() {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/session","root","root");
            statement = conn.createStatement();

            String sql = "select * from test_user";
            rs = statement.executeQuery(sql);
            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String age = rs.getString(3);
                System.out.println("id="+id+", name="+name+", age="+age);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(statement != null){
                    statement.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void insert(){
        Connection conn = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/session","root","root");
            statement = conn.createStatement();

            String sql = "insert into test_user(name, age) values('xiao', 12)";
            int result = statement.executeUpdate(sql);
            System.out.println("SQL 语句影响了："+result+"行");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(statement != null){
                        statement.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                    e.printStackTrace();
            }
        }
    }

    private static void delete(){
        Connection conn = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/session","root","root");
            statement = conn.createStatement();

            String sql = "delete from test_user where name = 'xiao'";
            int result = statement.executeUpdate(sql);
            System.out.println("SQL 语句影响了："+result+"行");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(statement != null){
                    statement.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void update(){
        Connection conn = null;
        Statement statement = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/session","root","root");
            statement = conn.createStatement();

            String sql = "update test_user set age = 21 where name = 'chen'";
            int result = statement.executeUpdate(sql);
            System.out.println("SQL 语句影响了："+result+"行");
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                if(statement != null){
                    statement.close();
                }
                if(conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
